package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface PromotionDao {

    @Select("select username,sex,telephone from users where userid=#{userid}")
    Map user(int userid);

    /**
     * 查询部门
     * @return
     */
    @Select("select * from depts where deptid!=5")
    List<Map> depts();

    /**
     * 查询职位
     * @return
     */
    @Select("select * from positions where pid!=2")
    List<Map> positions();

    /**
     * 根据职位名称查职位编号
     * @param pname
     * @return
     */
    @Select("select pid from positions where pname=#{pname}")
    int getIdbyPname(String pname);

    /**
     * 根据部门名称查询部门编号
     * @param dname
     * @return
     */
    @Select("select deptid from depts where dname=#{dname}")
    int deptidByDname(String dname);

    /**
     * 根据部门编号查询部门名称
     * @param deptid
     * @return
     */
    @Select("select dname from depts where deptid=#{deptid}")
    String dnameByDeptid(int deptid);

    /**
     * 根据职位id查询职位名称
     * @param positionid
     * @return
     */
    @Select("select pname from positions where pid=#{positionid}")
    String pnameByPositionid(int positionid);

    /**
     * 分页查询晋升发布
     * @param map
     * @return
     */
    @Select("<script>" +
            "select pro.id,d.dname,p.pname,pro.number,pro.requir,pro.duty," +
            "date_format(time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from promotion pro\n" +
            "left join depts d on d.deptid=pro.deptid\n" +
            "left join positions p on p.pid=pro.positionid\n" +
            "<where>" +
            "<if test=\"deptid!=null and deptid!=''\">and pro.deptid = #{deptid}</if>" +
            "</where>"+
            "order by pro.id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> proPage(Map map);

    /**
     * 查询晋升发布总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)\n"+
            "from promotion pro\n" +
            "left join depts d on d.deptid=pro.deptid\n" +
            "left join positions p on p.pid=pro.positionid\n" +
            "<where>" +
            "<if test=\"deptid!=null and deptid!=''\">and pro.deptid = #{deptid}</if>" +
            "</where>"+
            "</script>")
    int proPageCount(Map map);

    /**
     * 分页查询晋升发布历史记录
     * @param map
     * @return
     */
    @Select("<script>" +
            "select id,dname,pname,number,requir,duty," +
            "date_format(time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from promotion_history\n" +
            "<where>" +
            "<if test='dname!=null'>and dname like '%${dname}%'</if>" +
            "</where>"+
            "order by id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> proHistoryPage(Map map);

    /**
     * 查询晋升发布历史记录总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)"+
            "from promotion_history\n" +
            "<where>" +
            "<if test='dname!=null'>and dname like '%${dname}%'</if>" +
            "</where>"+
            "</script>")
    int proHistroryPageCount(Map map);

    /**
     * 分页查询晋升审核记录
     * @param map
     * @return
     */
    @Select("<script>" +
            "select cp.id,cp.userid,cp.username,cp.sex,cp.phone,d.dname,p.pname,cp.promotionid," +
            "date_format(cp.time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from check_promotion cp\n" +
            "left join positions p on p.pid=cp.pid\n" +
            "left join depts d on d.deptid=cp.deptid\n" +
            "<where>" +
            "<if test='username!=null'>and username like '%${username}%'</if>" +
            "</where>"+
            "order by id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> proCheckPage(Map map);

    /**
     * 分页查询晋升审核记录总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)"+
            "from check_promotion cp\n" +
            "left join positions p on p.pid=cp.pid\n" +
            "left join depts d on d.deptid=cp.deptid\n" +
            "<where>" +
            "<if test='username!=null'>and username like '%${username}%'</if>" +
            "</where>"+
            "</script>")
    int proCheckPageCount(Map map);

    /**
     * 分页查询晋升审核历史记录
     * @param map
     * @return
     */
    @Select("<script>" +
            "select id,username,sex,phone,dname,pname,result,reason," +
            "date_format(time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from promotion_record\n" +
            "<where>" +
            "<if test='username!=null'>and username like '%${username}%'</if>" +
            "</where>"+
            "order by id desc limit #{begin},#{pageSize}" +
            "</script>")
    List<Map> proRecordPage(Map map);

    /**
     * 查询晋升审核历史记录总数量
     * @param map
     * @return
     */
    @Select("<script>" +
            "select count(*)" +
            "from promotion_record\n" +
            "<where>" +
            "<if test='username!=null'>and username like '%${username}%'</if>" +
            "</where>"+
            "</script>")
    int proRecordPageCount(Map map);

    /**
     * 查询一条晋升发布记录
     * @param id
     * @return
     */
    @Select("<script>" +
            "select pro.id,d.dname,p.pname,pro.number,pro.requir,pro.duty," +
            "date_format(time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from promotion pro\n" +
            "left join depts d on d.deptid=pro.deptid\n" +
            "left join positions p on p.pid=pro.positionid\n" +
            "where pro.id=#{id}" +
            "</script>")
    Map proOne(int id);

    /**
     * 查询一条晋升审核记录
     * @param id
     * @return
     */
    @Select("<script>" +
            "select cp.id,cp.userid,cp.username,cp.sex,cp.phone,d.dname,p.pname,cp.promotionid," +
            "date_format(cp.time,'%Y-%m-%d %H:%i:%s') time\n" +
            "from check_promotion cp\n" +
            "left join positions p on p.pid=cp.pid\n" +
            "left join depts d on d.deptid=cp.deptid\n" +
            "where id=#{id}</script>")
    Map proCheckOne(int id);

    /**
     * 添加晋升发布信息
     * @param map
     * @return
     */
    @Insert("insert into promotion values(null,#{deptid},#{positionid},#{number}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{requir},#{duty})")
    int addPro(Map map);

    /**
     * 添加晋升发布信息历史记录
     * @param map
     * @return
     */
    @Insert("insert into promotion_history values(null,#{dname},#{pname},#{number}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{requir},#{duty})")
    int addProHistory(Map map);

    /**
     * 添加晋升申请
     * @param map
     * @return
     */
    @Insert("insert into check_promotion values(null,#{userid},#{username},#{sex},#{phone},#{deptid},#{pid}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{id})")
    int addProCheck(Map map);

    /**
     * 添加审核结果历史记录
     * @param map
     * @return
     */
    @Insert("insert into promotion_record values(null,#{username},#{sex},#{phone},#{dname},#{pname}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{result},#{reason})")
    int addProRecord(Map map);

    /**
     * 根据id修改晋升发布信息
     * @return
     */
    @Update("update promotion set deptid=#{deptid},positionid=#{pid},number=#{number},requir=#{requir},duty=#{duty}" +
            " where id=#{id}")
    int updatePro(Map map);

    /**
     * 根据id修改用户pid
     * @return
     */
    @Update("update users set positionid=#{pid} where userid=#{userid}")
    int updateUserOneOfPid(int pid,int userid);

    /**
     * 修改晋升信息人数-1
     * @param id
     * @return
     */
    @Update("update promotion set number=number-1 where id=#{id}")
    int updateProOfNumber(int id);

    /**
     * 删除晋升申请记录
     * @param id
     * @return
     */
    @Delete("delete from check_promotion where id=#{id}")
    int deletePro(int id);
}
